package com.nicy.dream.biz;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.nicy.dream.form.CourseEntity;
import com.nicy.dream.form.MsgClient;
import com.nicy.dream.form.StudentEntity;
import com.nicy.dream.form.TeacherEntity;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @description:
 * @projectName:dream
 * @see:com.nicy.dream.biz
 * @author:NicyYe
 * @createTime:2021/1/27 13:05
 * @version:1.0
 */
@Api(tags = "Excel测试控制器")
@RestController
@RequestMapping("/excel")
public class ExcelController {


    @ApiOperation(value = "导出csv")
    @GetMapping("/csv")
    public void exportCsv() throws IOException {
        List<MsgClient> list = new ArrayList<MsgClient>();
        Date start = new Date();
        CsvExportParams params = new CsvExportParams();
        for (int i = 0; i < 500; i++) {
            MsgClient client = new MsgClient();
            client.setBirthday(new Date());
            client.setClientName("xiaoming" + i);
            client.setClientPhone("18797" + i);
            client.setCreateBy("JueYue");
            client.setId("1" + i);
            client.setRemark("ceshi" + i);
            list.add(client);
        }

        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportBigData.bigDataExport.csv");
        //fos.write(Integer.parseInt(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF })));
        CsvExportUtil.exportCsv(params, MsgClient.class, list, fos);
        fos.flush();
        fos.close();
        System.out.println("导出完成" + (new Date().getTime() - start.getTime()));
    }



    @ApiOperation(value = "导出合并表格")
    @GetMapping("/xsl")
    public void exportXsl() throws IOException{
        //创建list
        List<CourseEntity> list = new ArrayList<>();
        //封装数据
        CourseEntity courseEntity = new CourseEntity();
        courseEntity.setId("001");
        courseEntity.setName("恋爱必修课(上)");
        //课程老师
        TeacherEntity teacherEntity = new TeacherEntity();
        teacherEntity.setTid("001");
        teacherEntity.setName("张三丰");
        courseEntity.setMathTeacher(teacherEntity.toString());

        //课程学生
        StudentEntity studentEntity = new StudentEntity();
        studentEntity.setId("001");
        studentEntity.setName("孙悟空");
        studentEntity.setSex("1");
        studentEntity.setBirthday(new Date());

        StudentEntity studentEntity2 = new StudentEntity();
        studentEntity2.setId("002");
        studentEntity2.setName("小乔");
        studentEntity2.setSex("");
        studentEntity2.setBirthday(new Date());

        //添加学生
        List<StudentEntity> userList= new ArrayList<>();
        userList.add(studentEntity);
        userList.add(studentEntity2);

        //添加课程
        courseEntity.setStudents(userList);

        //封装最后的一层数据
        list.add(courseEntity);

        //导出数据
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("课程信息",  "课程明细"),
                CourseEntity.class, list);

        //获取sheet对象
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取表格列数
        int lastRowNum = sheetAt.getLastRowNum();
        //获取列数
        int physicalNumberOfCells = sheetAt.getRow(0).getPhysicalNumberOfCells();

        //开始遍历表格并进行判断是否渲染
        for (int i=3;i<=lastRowNum;i++){
            //获取每行对象
            Row row = sheetAt.getRow(i);
            //for (int j=0;j<physicalNumberOfCells;j++){

                //获取单元格对象
                Cell cell = row.getCell(0);
                CellStyle cellStyle= workbook.createCellStyle();

                //获取单元格内容对象
                Font font = workbook.createFont();
                //内容换行
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                cellStyle.setWrapText(true);
                cell.setCellStyle(cellStyle);
            //}

        }


        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }

        //Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), CompanyHasImgModel.class, list);
        FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportHasImgTest.exportCompanyImg.xls");
        workbook.write(fos);
        fos.close();

    }


    @ApiOperation(value = "导出多sheet页")
    @GetMapping("/getMoreSheet")
    public void exporMoreSheet(HttpServletResponse response){
        //定义学生集合(模拟数据)
        List<StudentEntity> studentEntityList = new ArrayList<>();
        //封装学生数据
        StudentEntity studentEntity1= new StudentEntity();
        studentEntity1.setBirthday(new Date());
        studentEntity1.setSex("1");
        studentEntity1.setName("zhang3");
        studentEntity1.setId("1");
        studentEntity1.setRegistrationDate(new Date());
        //添加到集合列表
        studentEntityList.add(studentEntity1);


        //定义老师集合（模拟数据）
        List<TeacherEntity> teacherEntityList = new ArrayList<>();
        //封装老师集合
        TeacherEntity teacherEntity1 = new TeacherEntity();
        teacherEntity1.setName("理查德");
        teacherEntity1.setTid("001");
        teacherEntity1.setRemark("这是一个备注信息");
        //添加到教师集合
        teacherEntityList.add(teacherEntity1);

        Workbook workBook = null;
        try {
            // 创建参数对象（用来设定excel得sheet得内容等信息）
            ExportParams deptExportParams = new ExportParams();
            // 设置sheet得名称
            deptExportParams.setSheetName("学生信息明细");
            // 设置sheet表头名称
            deptExportParams.setTitle("学生信息");
            // 创建sheet1使用得map
            Map<String, Object> deptExportMap = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            deptExportMap.put("title", deptExportParams);
            // 模版导出对应得实体类型
            deptExportMap.put("entity", StudentEntity.class);
            // sheet中要填充的数据
            deptExportMap.put("data", studentEntityList);
            ExportParams empExportParams = new ExportParams();
            empExportParams.setTitle("老师信息");
            empExportParams.setSheetName("教师信息明细");
            // 创建sheet2使用得map
            Map<String, Object> empExportMap = new HashMap<>();
            empExportMap.put("title", empExportParams);
            empExportMap.put("entity", TeacherEntity.class);
            empExportMap.put("data", teacherEntityList);
            // 将sheet1、sheet2使用得map进行包装
            List<Map<String, Object>> sheetsList = new ArrayList<>();
            sheetsList.add(deptExportMap);
            sheetsList.add(empExportMap);
            // 执行方法
            workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
            //String fileName = URLEncoder.encode("test", "UTF-8");
            //String filepath = (String) LoadStaticConfigUtil.getCommonYml( "testcaseexcel.cases");
            //FileOutputStream fos = new FileOutputStream("D:/excel/more.xls");
            //workBook.write(fos);
            //fos.close();
            String fileName = "齐齐哈尔市第一医院"+new SimpleDateFormat("yyyy年MM月dd日").format(new Date())+"日报";
            fileName = URLEncoder.encode(fileName, "UTF8");
            response.setContentType("application/vnd.ms-excel;chartset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="+fileName + ".xls");
            ServletOutputStream out=response.getOutputStream();
            workBook.write(out);
            out.flush();
            out.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
